package com.lovestudying.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import java.util.Vector;

import com.lovestudying.dao.CourseDao;
import com.lovestudying.dao.TabDao;
import com.lovestudying.entity.Course;
import com.lovestudying.entity.Tab;
import com.lovestudying.entity.Teacher;
import com.lovestudying.util.DBUtil;
import com.lovestudying.util.DBUtil_Pro;
import com.lovestudying.util.PageInfo;

/**
 * Course Dao 实现类
 * 
 * @author OldDeer
 *
 */
public class CourseDaoImpl implements CourseDao {
	TabDao tabDao = new TabDaoImpl();

	/*
	 * （非 Javadoc）
	 * 
	 * @see com.lovestudying.dao.CourseDao#DeleteCourse(java.util.Map)
	 */
	@Override
	public Integer DeleteCourse(Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("DELETE FROM course");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			for (Entry<String, Object> element : Where_Param.entrySet()) {
				sql.append(" " + element.getKey() + " = ?");
				params.add(element.getValue());
				i++;
				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}
		// System.out.println(sql.toString());
		// System.out.println(params);
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	/*
	 * （非 Javadoc）
	 * 
	 * @see com.lovestudying.dao.CourseDao#InsertCourse(java.util.Map)
	 */
	@Override
	public Integer InsertCourse(Map<String, Object> Value_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("INSERT INTO  Course (");
		// values参数列表
		Vector<Object> params = new Vector<>();
		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Value_Param.entrySet()) {
			sql.append(element.getKey());
			params.add(element.getValue());
			i++;
			if (i < Value_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(") VALUES (");
		for (int j = 0; j < params.size(); j++) {
			sql.append("?");
			if (j < params.size() - 1) {
				sql.append(",");
			}
		}
		sql.append(")");
		// System.out.println(sql.toString());
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	/*
	 * （非 Javadoc）
	 * 
	 * @see com.lovestudying.dao.CourseDao#UpdateCourse(java.util.Map,
	 * java.util.Map)
	 */
	@Override
	public Integer UpdateCourse(Map<String, Object> Set_Param, Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("UPDATE Course SET");
		// 参数列表
		Vector<Object> params = new Vector<>();

		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Set_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			i++;
			if (i < Set_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(" WHERE");
		int j = 0;
		for (Entry<String, Object> element : Where_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			j++;
			if (j < Where_Param.size()) {
				sql.append(" and");
			}
		}
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	/*
	 * （非 Javadoc）
	 * 
	 * @see
	 * com.lovestudying.dao.CourseDao#findCourse_Where_OrderBy_Limit(java.util.Map,
	 * java.util.Map, java.lang.Integer, java.lang.Integer)
	 */
	@Override
	public PageInfo<Course> findCourseUniTeacher(Map<String, Object> Where_Param,
			Map<String, Object> OrdeBy_Param, Integer Lmt_pageSize, Integer Lmt_pageNum) {
		List<Course> list = new Vector<>();
		PageInfo<Course> CoursePage;
		ResultSet rs = null;
		// 创建sql语句
		StringBuffer sql = new StringBuffer(
				// Updatable 左外连接内联查询显示问题
				"SELECT * FROM Course course LEFT JOIN Teacher teacher ON course.teacher_id = teacher.teacher_id");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			// 遍历Where参数列表,将key拼接到sql字符串中，value则添加进params参数列表里
			for (Entry<String, Object> element : Where_Param.entrySet()) {

				if (element.getKey().contains("teacher_id") || element.getKey().contains("course_title")) {
					sql.append(" " + element.getKey() + " like ?");
					params.add("%" + element.getValue() + "%");
					i++;
				} else {
					sql.append(" " + element.getKey() + " = ?");
					params.add(element.getValue());
					i++;
				}
				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}
		// 分页中的total
		int total = 0;
		try {
			ResultSet last = DBUtil_Pro.doQuery(sql.toString(), params);
			last.last();
			total = last.getRow();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		// 添加OrderBy参数
		if (OrdeBy_Param != null && OrdeBy_Param.size() != 0) {
			sql.append(" ORDER BY");
			int i = 0;
			// 遍历OrderBy参数列表,将key拼接到sql字符串中，value为null则不操作，不为空则对sql拼接上倒序 DESC
			for (Entry<String, Object> element : OrdeBy_Param.entrySet()) {
				sql.append(" " + element.getKey());
				// 做升降序设置，键的值是null则是默认的ASC，不为null则添加DESC
				if (element.getValue() != null) {
					sql.append(" DESC");
				}
				i++;
				if (i < OrdeBy_Param.size()) {
					sql.append(",");
				}
			}
		}
		// 添加Limit参数，两者都不为空才添加Limit关键字
		if (Lmt_pageNum != null && Lmt_pageSize != null && Lmt_pageNum != 0) {
			sql.append(" LIMIT ?,?");
			params.add((Lmt_pageNum - 1) * Lmt_pageSize);
			params.add(Lmt_pageSize);
		}

		// 执行SQL语句
		rs = DBUtil_Pro.doQuery(sql.toString(), params);
		try {
			while (rs.next()) {
				// 根据course_id检索标签列表
				Map<String, Object> TabMap = new HashMap<String, Object>();
				TabMap.put("course_id", rs.getString("course_id"));
				List<Tab> data = tabDao.findTab_Where_OrderBy_Limit(TabMap, null, null, null).getData();
				// 创建课程实例
				Course course = new Course(rs.getString("course_id"), rs.getString("course_title"),
						new Teacher(rs.getString("teacher_id"), rs.getString("teacher_name"),
								rs.getString("teacher_preview"), rs.getString("teacher_pic")),
						rs.getDouble("course_price"), rs.getString("course_create_time"), rs.getInt("pay_type"), data,
						rs.getString("course_introdu"), rs.getString("course_pic"));
				list.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		CoursePage = new PageInfo<>(list, Lmt_pageNum, Lmt_pageSize, total);
		return CoursePage;
	}

	@Override
	public PageInfo<Course> findCourseInformation(Map<String, String> params) {
		String courseTitleValue = params.get("courseTitle");
		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
		List<Course> list = new ArrayList<>();
		StringBuffer sBuffer = new StringBuffer();
		ResultSet rs = null;
		sBuffer.append(" select ").append(" A.course_id, ").append(" A.course_title, ").append(" B.teacher_name,")
				.append(" A.course_price, ").append(" A.course_create_time, ").append(" A.pay_type, ")
				.append(" A.course_pic, ").append(" A.course_introdu ").append(" from ").append(" course A ")
				.append(" left join teacher B").append(" on A.teacher_id = B.teacher_id ");
		if ("".equals(courseTitleValue)) {
			sBuffer.append("limit ?,?");
			rs = DBUtil.find(sBuffer.toString(), (pageNum - 1) * pageSize, pageSize);
		} else {
			sBuffer.append("where course_title like ? limit ?,?");
			rs = DBUtil.find(sBuffer.toString(), courseTitleValue, (pageNum - 1) * pageSize, pageSize);
		}
		try {
			while (rs.next()) {
				Course course = new Course(rs.getString("course_id"), rs.getString("course_title"),
						new Teacher(null, rs.getString("teacher_name"), null, null), rs.getDouble("course_price"),
						rs.getString("course_create_time"), rs.getInt("pay_type"), null, rs.getString("course_introdu"),
						rs.getString("course_pic"));
				list.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Integer total = 0;
		rs = DBUtil.find("select count(*) from course Where course_title like ?", courseTitleValue);// 总条数
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Course> pageInfo = new PageInfo<>(list, pageNum, pageSize, total);

		return pageInfo;
	}

	@Override
	public List<Course> findCourseByOrderId(String orderId) {
		// 初始化一个集合
		List<Course> list = new ArrayList<>();

		// 获取结果集
		ResultSet rSet = DBUtil.find(
				"select B.course_id,B.course_title,B.course_price,B.course_create_time,B.pay_type,C.teacher_name \r\n"
						+ "from order_detail A inner join course B on A.course_id = B.course_id\r\n"
						+ "left join teacher C on C.teacher_id = B.teacher_id\r\n" + "where A.order_id = ?",
				orderId);

		try {
			while (rSet.next()) {

				// 获取个字段信息
				String courseId = rSet.getString("course_id");
				String courseTitle = rSet.getString("course_title");
				Double coursePrice = rSet.getDouble("course_price");
				String courseCreateTime = rSet.getString("course_create_time");
				Integer payType = rSet.getInt("pay_type");
				String teacherName = rSet.getString("teacher_name");

				// 创建老师对象
				Teacher teacher = new Teacher();
				teacher.setTeacher_name(teacherName);

				// 创建课程对象
				Course course = new Course();
				course.setCourse_id(courseId);
				course.setCourse_create_time(courseCreateTime);
				course.setCourse_price(coursePrice);
				course.setCourse_title(courseTitle);
				course.setPay_type(payType);

				course.setTeacher(teacher);

				list.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public List<Course> findCourseUniTab(List<String> courseTab) {
		List<Course> list = new Vector<>();
		Vector<Object> params = new Vector<>();
		StringBuffer sql = new StringBuffer(
				"SELECT DISTINCT tab.course_id, course.course_title ,course.teacher_id,course.course_price,course_pic FROM Course course INNER JOIN Tab tab ON tab.course_id = course.course_id");
		if (courseTab.size() != 0) {
			sql.append(" WHERE");
			for (int i = 0; i < courseTab.size(); i++) {
				sql.append(" course_tab like ?");
				params.add(courseTab.get(i));
				if (i < courseTab.size() - 1) {
					sql.append(" or");
				}
			}
		}

		ResultSet rSet = DBUtil_Pro.doQuery(sql.toString(), params);
		// System.out.println(sql.toString());
		try {
			while (rSet.next()) {
				list.add(new Course(rSet.getString("course_id"), rSet.getString("course_title"),
						new Teacher(rSet.getString("teacher_id"), null, null, null), rSet.getDouble("course_price"),
						null, null, null, null, rSet.getString("course_pic")));

			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

		return list;

	}

	@Override
	public PageInfo<Course> findCourseInformationByKeys(Map<String, String> params) {
		String courseTitleValue = params.get("courseTitle");
		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
		StringBuffer sql = new StringBuffer(
				"SELECT DISTINCT course.course_id , course.course_title,course.course_price,course.course_create_time,course.course_introdu,course.course_pic,teacher.teacher_name, course.pay_type FROM Course course ");
		sql.append("LEFT JOIN Teacher teacher ON teacher.teacher_id = course.teacher_id ");
		sql.append("LEFT JOIN Tab tab ON tab.course_id = course.course_id ");
		sql.append("WHERE course_tab LIKE ? OR teacher_name LIKE ? OR course_title LIKE ? ");
		int total = 0;
		try {
			ResultSet last = DBUtil.find(sql.toString(), courseTitleValue, courseTitleValue, courseTitleValue);
			last.last();
			total = last.getRow();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		sql.append("LIMIT ?,?");
		ResultSet rSet = DBUtil.find(sql.toString(), courseTitleValue, courseTitleValue, courseTitleValue,
				(pageNum - 1) * pageSize, pageSize);

		List<Course> list = new Vector<>();
		try {
			while (rSet.next()) {
				Course course = new Course(rSet.getString("course_id"), rSet.getString("course_title"),
						new Teacher(null, rSet.getString("teacher_name"), null, null), rSet.getDouble("course_price"),
						rSet.getString("course_create_time"), rSet.getInt("pay_type"), null,
						rSet.getString("course_introdu"), rSet.getString("course_pic"));
				list.add(course);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		PageInfo<Course> pageInfo = new PageInfo<>(list, pageNum, pageSize, total);
		return pageInfo;
	}

}
